//-----------------------------------------------------------------------------
// Filename: DBAuthenticator.cs
//
// Description: 
// Provides authentication services from a database.
// 
// History:
// 19 Oct 2004	Aaron Clauson	Created.
// 11 Dec 2004	Aaron Clauson	Refactored for AuthenticatorProduct factory.
// 18 Dec 2004	Aaron Clauson	Added GetPassword and SetTimeout methods.
//
// License: Public Domain.
//-----------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Xml;
using MaverickLite.Config;
using BlueFace.Sys;
using log4net;

namespace MaverickLite.Security
{	
	/// <summary>
	/// 
	/// <autheticationstore id="admin" type="database">
	///  <storagetype>Postgresql</storagetype>
	///  <connectionstring>Server=192.168.1.32;Port=5432;User Id=username;Password=password;Database=whitelabel-crm;MaxPoolSize=10;MinPoolSize=1;Timeout=5;Encoding=UNICODE;</connectionstring>
	///  <credentialstable>adminusers</credentialstable>
	///  <usernamefield>adminuser</usernamefield>
	///  <passwordfield>password</passwordfield>
	///  <sessionstable>adminsessions</sessionstable>
	///  <timeout>240</timeout>					
	///  </autheticationstore>
	///  
	/// </summary>
	public class DBAuthenticationStore
	{
		private static ILog logger = MavAppState.logger;
		
		public StorageTypes DBStorageType;
		public string DBConnStr;
		public string CredentialsTable;
		public string UsernameField;
		public string PasswordField;
		public string SessionsTable;
		public int Timeout = AuthenticatorProduct.AUTHENTICATION_SESSION_TIMEOUT;	// Session timeout in minutes.

		public DBAuthenticationStore(
			StorageTypes dbStorageType,
			string dbConnStr,
			string credentialsTable,
			string usernameField,
			string passwordField,
			string sessionsTable,
			int timeout)
		{
			DBStorageType = dbStorageType;
			DBConnStr = dbConnStr;
			CredentialsTable = credentialsTable;
			UsernameField = usernameField;
			PasswordField = passwordField;
			SessionsTable = sessionsTable;
			Timeout = timeout;
		}

		public DBAuthenticationStore(string authStoreXML)
		{
			try
			{
				XmlDocument authStoreDOM = new XmlDocument();
				authStoreDOM.LoadXml(authStoreXML);

				DBStorageType = StorageTypesConverter.GetStorageType(authStoreDOM.DocumentElement.SelectSingleNode("storagetype").InnerText);
				DBConnStr = authStoreDOM.DocumentElement.SelectSingleNode("connectionstring").InnerText;
				CredentialsTable = authStoreDOM.DocumentElement.SelectSingleNode("credentialstable").InnerText;
				UsernameField = authStoreDOM.DocumentElement.SelectSingleNode("usernamefield").InnerText;
				PasswordField = authStoreDOM.DocumentElement.SelectSingleNode("passwordfield").InnerText;
				SessionsTable = authStoreDOM.DocumentElement.SelectSingleNode("sessionstable").InnerText;
				Timeout = Convert.ToInt32(authStoreDOM.DocumentElement.SelectSingleNode("timeout").InnerText);
			}
			catch(Exception excp)
			{
				logger.Error("Exception DBAuthenticationStore. " + excp.Message);
			}
		}
	}
	
	/// <summary>
	/// The class that does the authentication and maintains authentication sessions by 
	/// using information stored in a databse.
	/// 
	/// A database table with a username and password field to authenticate against and a 
	/// table to record authenticated sessions.
	/// 
	/// create table authenticatedsessions
	/// (
	///  sessionid varchar(36) not null unique,
	///  userid varchar(36) not null,
	///  ipaddress varchar(15),
	///  starttime timestamptz not null default now(),
	///  lastcontacttime timestamptz not null default now(),
	///  expired bit not null default '0',
	///  Primary Key(sessionid),
	/// );
	///  
	/// </summary>
	public class DBAuthenticator : AuthenticatorProduct
	{
		private static ILog logger = MavAppState.logger;

		private DBAuthenticationStore m_dbAuthStore;
		private StorageLayer m_storageLayer = new StorageLayer();
		private StorageTypes m_storageType = StorageTypes.Postgresql;

		public DBAuthenticator(DBAuthenticationStore authenticationStore)
		{
			m_dbAuthStore = authenticationStore;
			m_storageLayer = new StorageLayer(authenticationStore.DBStorageType, authenticationStore.DBConnStr);
			m_storageType = authenticationStore.DBStorageType;
			this.m_sessionDuration = new TimeSpan(0, m_dbAuthStore.Timeout, 0);
		}

		public override bool Authenticate(Credentials credentials, string ipAddress)
		{
			string sessionId = AuthenticateWebRequest(credentials, ipAddress);

			if(sessionId != null)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		
		/// <summary>
		/// Check with the directory whether a provided authentication id corresponds
		/// to an established session.
		/// </summary>
		/// <param name="authenticationId">An id possibly representing an established 
		/// authenticated session</param>
		/// <returns>True if authentication id valid, otherwise false.</returns>
		public override bool Authenticate(string authenticationId)
		{
			try
			{
				DateTime startAuthTime = DateTime.Now;
				
				if(authenticationId == null || authenticationId.Length == 0)
				{
					return false;
				}
				
				// Audit this method.
				//logger.Info("Authenticate called for authenticationId " + authenticationId + ".");

				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(m_storageType, m_databaseConnectionString);

				DataSet lastTimeDS = null;
				if(m_storageType == StorageTypes.MSSQL)
				{
					lastTimeDS = m_storageLayer.GetDataSet("select LastContactTime, GetDate() from " + m_dbAuthStore.SessionsTable + " where SessionId = '" + authenticationId + "' and Expired <> 1");
				}
				else if(m_storageType == StorageTypes.Postgresql)
				{
					lastTimeDS = m_storageLayer.GetDataSet("select LastContactTime, now() from " + m_dbAuthStore.SessionsTable + " where SessionId = '" + authenticationId + "' and Expired <> '1'");
				}
				else
				{
					throw new ApplicationException("This storage type is not supported in Authenticate (authid).");
				}

				DateTime sessionTimestamp = DateTime.MinValue;
				DateTime currentTimestamp = DateTime.MinValue;

				if(lastTimeDS != null && lastTimeDS.Tables[0].Rows.Count > 0)
				{
					sessionTimestamp = (DateTime)lastTimeDS.Tables[0].Rows[0][0];
					currentTimestamp = (DateTime)lastTimeDS.Tables[0].Rows[0][1];
				}
				else
				{
					logger.Warn("Authenticate was called with an unknown authentication session id, " + authenticationId + ".");

					return false;
				}

				if(currentTimestamp - sessionTimestamp < m_sessionDuration)
				{			
					// Session is valid update timestamp.
					if(m_storageType == StorageTypes.MSSQL)
					{
						m_storageLayer.ExecuteNonQuery("update " + m_dbAuthStore.SessionsTable + " set LastContactTime = GetDate() where SessionId = '" + authenticationId + "'");
					}
					else if(m_storageType == StorageTypes.Postgresql)
					{
						m_storageLayer.ExecuteNonQuery("update " + m_dbAuthStore.SessionsTable + " set LastContactTime = now() where SessionId = '" + authenticationId + "'");
					}
					else
					{
						throw new ApplicationException("This storage type is not supported in Authenticate (authid).");
					}

					//logger.Debug("Authenticator updating the timestamp for valid session id, " + authenticationId + ".");

					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("Authentication sessionid) time = " + authTime.TotalMilliseconds + " (ms).");

					return true;
				}
				else
				{
					//logger.Debug("Authenticator was called on an expired session. The session timestamp was " + sessionTimestamp.ToString(TIMESTAMP_FORMAT) + ", the current time is " + currentTimestamp.ToString(TIMESTAMP_FORMAT) + ".");
					
					// Session is invalid remove session entry.
					m_storageLayer.ExecuteNonQuery("update " + m_dbAuthStore.SessionsTable + " set Expired = '1' where SessionId = '" + authenticationId + "'");

					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("Authentication (sessionid) time = " + authTime.TotalMilliseconds + " (ms).");

					return false;
				}
			}
			catch(Exception excp)
			{
				logger.Error("Exception Authenticate (authenticationId): " + excp.Message);
				return false;
			}
		}

		
		public override string GetUsernameForSessionId(string authenticationSessionId)
		{
			//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
			//object conn = StorageLayer.GetConnection(m_storageType, m_databaseConnectionString);

			//DataSet authDS = StorageLayer.GetDataSet(m_storageType, conn, "select Username from Customers cs right outer join AuthenticatedSessions auth on cs.CustomerId = auth.CustomerId where SessionId = '" + authenticationSessionId + "'");
			string username = m_storageLayer.ExecuteScalar("select " + m_dbAuthStore.UsernameField + " from " + m_dbAuthStore.SessionsTable + " where sessionid = '" + authenticationSessionId + "'") as string;

			return username;

			/*if(authDS != null && authDS.Tables[0].Rows.Count > 0)
			{
				return (string)authDS.Tables[0].Rows[0][0];
			}
			else
			{
				logger.Error("Authenticator could not extract the username for an authentication session id, " + authenticationSessionId + ".");
				return null;
			}*/
		}

		
		/// <summary>
		/// Checks with the directory to determine the validity of a provided username
		/// and password combination.
		/// </summary>
		/// <param name="username"></param>
		/// <param name="password"></param>
		/// <returns>True if credentials valid, otherwise false.</returns>
		public override bool Authenticate(string username, string password)
		{
			try
			{
				DateTime startAuthTime = DateTime.Now;
				
				if(username == null || username.Trim().Length == 0)
				{
					return false;
				}

				// Audit this method.
				//logger.Info("Authenticate called for username " + username + ".");
				
				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(StorageTypes.Postgresql, m_databaseConnectionString);

				int count = 0;
				if(m_storageType == StorageTypes.MSSQL)
				{
					count = Convert.ToInt32(m_storageLayer.ExecuteScalar("select count(*) from " + m_dbAuthStore.CredentialsTable + " where " + m_dbAuthStore.UsernameField + " = '" + username + "' and " + m_dbAuthStore.PasswordField + " = '" + password.Replace("'", "''") + "' and suspended <> '1'"));
				}
				else if(m_storageType == StorageTypes.Postgresql)
				{
					// Username is case insensitive and password is case sensitive.
					//string selectSQL = "select count(*) from Customers where Username ~* '^" + username + "$' and Password = '" + password + "'";
					string selectSQL = "select count(*) from " + m_dbAuthStore.CredentialsTable + " where upper(" + m_dbAuthStore.UsernameField + ") = '" + username.ToUpper() + "' and " + m_dbAuthStore.PasswordField + " = '" + password.Replace("'", "''") + "' and suspended <> '1'";
					string countResult = m_storageLayer.ExecuteScalar(selectSQL).ToString();

					try
					{
						count = Convert.ToInt32(countResult);
					}
					catch(Exception excp)
					{
						logger.Error("Exception determining username count for, " + selectSQL + ". The result was " + countResult + ". " + excp.Message);
					}
				}
				else
				{
					throw new ApplicationException("This storage type is not supported in Authenticate (username, password).");
				}

				if(count == 1)
				{
					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("Authentication (username/password) time = " + authTime.TotalMilliseconds + " (ms).");

					return true;
				}
				else
				{
					logger.Warn("Authenticate was called with an invalid/unknown username or password combination " + username + ", " + password + ".");

					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("Authentication (username/password) time = " + authTime.TotalMilliseconds + " (ms).");

					return false;
				}
			}
			catch(Exception excp)
			{
				logger.Error("Exception Authenticate (username): " + excp.Message);
				return false;
			}
		}


		public override void CreateSession(string username, string authenticationId, string ipAddress)
		{
			try
			{
				logger.Debug("Creating new authenticated session for " + username + ", with id, " + authenticationId + ".");
				
				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(m_storageType, m_databaseConnectionString);

				// Get the customer id for this username.
				/*string selectSQL = null;
				if(m_storageType == StorageTypes.MSSQL)
				{
					selectSQL = "select CustomerId from Customers where Username = '" + username + "'";
				}
				else if(m_storageType == StorageTypes.Postgresql)
				{
					selectSQL = "select CustomerId from Customers where upper(username) = '" + username.ToUpper() + "'";
				}
				else
				{
					throw new ApplicationException("Storage type not supported in CreateSession.");
				}*/

				/*DataSet authDS = m_storageLayer.GetDataSet(selectSQL);

				Guid customerId = Guid.Empty;
				if(authDS.Tables[0].Rows.Count == 1)
				{
					try
					{
						customerId = new Guid(authDS.Tables[0].Rows[0][0].ToString());
					}
					catch(Exception excp)
					{
						logger.Error("Error retrieving customerid in CreateSession. SQL: " + selectSQL + ". DB Result: " + authDS.Tables[0].Rows[0][0].ToString() + ". " + excp.Message);
					}
				}
				else
				{
					logger.Error("CreateSession could not get customer from database: " + selectSQL);
				}*/

				// Remove any existing sessions.
				//StorageLayer.ExecuteNonQuery(m_storageType, conn, "update AuthenticatedSessions set Expired = '1' where CustomerId = '" + customerId + "'");
	
				// Create new session.
				//if(customerId != Guid.Empty)
				//{
					m_storageLayer.ExecuteNonQuery("insert into " + m_dbAuthStore.SessionsTable + " (sessionid, " + m_dbAuthStore.UsernameField + ", ipaddress) values ('" + authenticationId + "', '" + username + "', '" + ipAddress + "')");
				//}
				//else
				//{
				//	logger.Error("CreateSession did not create a new session because the CustomerID had not been assigned.");
				//}
			}
			catch(Exception excp)
			{
				logger.Error("Exception CreateSession: " + excp.Message);
			}
		}

		
		/// <summary>
		/// Attempts to authenticate against the provided credentials.
		/// </summary>
		/// <param name="requestCredentials">Set of credentials from an incoming web request.</param>
		/// <returns>If authenticated a valid session id, otherwise null.</returns>
		public override string AuthenticateWebRequest(Credentials requestCredentials, string ipAddress)
		{
			try
			{
				DateTime startAuthTime = DateTime.Now;
				
				//logger.Debug("AuthenticateWebRequest using database authenticator.");
				
				if( requestCredentials == null || (requestCredentials.Username == null || requestCredentials.Username.Trim().Length == 0)&& (requestCredentials.AuthenticatedSessionId == null || requestCredentials.AuthenticatedSessionId.Trim().Length == 0))
				{
					logger.Debug("AuthenticateWebRequest has null credentials returning.");
					return null;
				}
				else
				{
					logger.Info("AuthenticateWebRequest: username = " + requestCredentials.Username + "\r\n"
						//+ " password = " + requestCredentials.Password + "\r\n" 
						+ " authentication session id = " + requestCredentials.AuthenticatedSessionId + ".");
				}

				// If an authentication id is present check with the directory
				// to see whether it is valid.
				if(requestCredentials.AuthenticatedSessionId != null)
				{
					if(Authenticate(requestCredentials.AuthenticatedSessionId))
					{
						logger.Info("AuthenticateWebRequest: authenticated session id " + requestCredentials.AuthenticatedSessionId 
							+ " for user " + GetUsernameForSessionId(requestCredentials.AuthenticatedSessionId) + " at " + DateTime.Now.ToString(TIMESTAMP_FORMAT) + ".");

						TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
						logger.Debug("AuthWebRequest time = " + authTime.TotalMilliseconds + " (ms).");

						UpdateSessionContactTime(requestCredentials.AuthenticatedSessionId);
						
						return requestCredentials.AuthenticatedSessionId;
					}
				}

				// If not authenticated from state information check for valid username and password information.
				if(!Authenticate(requestCredentials.Username, requestCredentials.Password))
				{
					logger.Warn("AuthenticateWebRequest: authentication for user " + requestCredentials.Username 
						+ " failed at " +  DateTime.Now.ToString(TIMESTAMP_FORMAT) + ".");

					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("AuthWebRequest time = " + authTime.TotalMilliseconds + " (ms).");
					
					return null;
				}
				else
				{
					string sessionId = Guid.NewGuid().ToString();
					
					logger.Info("AuthenticateWebRequest: authenticated user " + requestCredentials.Username 
							+ " authentication session id assigned " + sessionId + " at " + DateTime.Now.ToString(TIMESTAMP_FORMAT) + ".");

					TimeSpan authTime = DateTime.Now.Subtract(startAuthTime);
					logger.Debug("AuthWebRequest time = " + authTime.TotalMilliseconds + " (ms).");

					// Create a session in the directory.
					CreateSession(requestCredentials.Username, sessionId, ipAddress);

					return sessionId;
				}
			}
			catch(Exception excp)
			{
				logger.Error("Exception AuthenticateWebRequest: " + excp.Message);
				return null;
			}
		}


		public override void UpdateSessionContactTime(string sessionId)
		{
			try
			{
				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(StorageTypes.Postgresql, m_databaseConnectionString);

				// Expire session.
				m_storageLayer.ExecuteNonQuery("update " + m_dbAuthStore.SessionsTable + " set lastcontacttime = now() where SessionId = '" + sessionId + "'");
			}
			catch(Exception excp)
			{
				logger.Error("Exception UpdateSessionContactTime: " + excp.Message);
			}
		}

	
		public override void RemoveSession(string session)
		{
			try
			{
				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(StorageTypes.Postgresql, m_databaseConnectionString);

				// Expire session.
				m_storageLayer.ExecuteNonQuery("update " + m_dbAuthStore.SessionsTable + " set Expired = '1' where SessionId = '" + session + "'");
			}
			catch(Exception excp)
			{
				logger.Error("Exception RemoveSession: " + excp.Message);
			}
		}

	
		public override DateTime GetSessionTimeout(string sessionId)
		{
			try
			{
				//SqlService authenticationDb = new SqlService(m_databaseConnectionString);
				//object conn = StorageLayer.GetConnection(StorageTypes.Postgresql, m_databaseConnectionString);

				DataSet lastTimeDS = m_storageLayer.GetDataSet("select LastContactTime from " + m_dbAuthStore.SessionsTable + " where SessionId = '" + sessionId + "' and Expired <> '1'");

				DateTime sessionTimestamp = DateTime.MinValue;

				if(lastTimeDS != null)
				{
					sessionTimestamp = (DateTime)lastTimeDS.Tables[0].Rows[0][0];
					DateTime timestamp = sessionTimestamp.AddMinutes(AUTHENTICATION_SESSION_TIMEOUT);
					
					//logger.Info("GetSessionTimeout returning " + timestamp.ToString(TIMESTAMP_FORMAT) + " for session id " + sessionId + " ( " + GetUsernameForSessionId(sessionId) + " ).");

					return timestamp;
				}
				else
				{
					logger.Warn("GetSessionTimeout was requested to get a timestamp for an invalid or expired session id, " + sessionId + " ( " + GetUsernameForSessionId(sessionId) + " ).");

					return DateTime.MinValue;
				}
			}
			catch(Exception excp)
			{
				logger.Error("Exception GetSessionTimeout: " + excp.Message);
				return DateTime.MinValue;
			}
		}
		
	
		public override void RemoveSession(Credentials requestCredentials)
		{

		}

		/// <summary>
		/// Retrieves the password for an account for the given account name or email address.
		/// </summary>
		/// <param name="account">A string parameter that represents an account name or an email address.</param>
		/*public override void GetPassword(string account, out string emailAddress, out string username, out string password)
		{
			//logger.Debug("Attempting to get password details for account " + account + ".");
			
			emailAddress = null;
			username = null;
			password = null;

			if(account == null || account.Trim().Length == 0)
			{
				return;
			}

			//SqlConnection authenticationDb = new SqlConnection(m_databaseConnectionString);
			//object conn = StorageLayer.GetConnection(m_storageType, m_databaseConnectionString);

			string select = null;
			if(m_storageType == StorageTypes.MSSQL)
			{
				select = "select emailaddress, username, password from Customers cu join CustomerEmailAddresses ce " +
					" on cu.customerid = ce.customerid " +
					"where emailaddress = '" + account + "' or username='" + account + "'";
			}
			else if(m_storageType == StorageTypes.Postgresql)
			{
				select = "select emailaddress, username, password from Customers cu join CustomerEmailAddresses ce " +
					" on cu.customerid = ce.customerid " +
					"where emailaddress ~* '^" + account + "$' or upper(username) = '" + account.ToUpper() + "'";}
			else
			{
				throw new ApplicationException("This storage type is not supported in GetPassword.");
			}
			
			//logger.Debug("Get user details select = " + select);

			try
			{
				DataSet detailsDs = m_storageLayer.GetDataSet(select);

				if(detailsDs.Tables[0].Rows.Count > 0)
				{
					DataRow detailsRow = detailsDs.Tables[0].Rows[0];

					emailAddress = detailsRow["emailaddress"] as string;
					username = detailsRow["username"] as string;
					password = detailsRow["password"] as string;
				}
			}
			catch(Exception excp)
			{
				logger.Warn("Exception DBAuthenticator GetPassword: " + excp.Message);
				throw excp;
			}
		}*/

		/// <summary>
		/// Sets the period that authenticated sessions will remain valid for.
		/// </summary>
		/// <param name="duration"></param>
		public override void SetTimeout(TimeSpan duration)
		{
			m_sessionDuration = duration;
		}
	}
}
